"""
病历 CRUD 操作
"""
from typing import List, Optional, Tuple
from sqlalchemy import select, desc, asc, func
from sqlalchemy.ext.asyncio import AsyncSession

from  crud.base import CRUDBase
from  models.medical_record import MedicalRecord
from  schemas.medical_record import MedicalRecordCreate, MedicalRecordUpdate


class CRUDMedicalRecord(CRUDBase[MedicalRecord, MedicalRecordCreate, MedicalRecordUpdate]):
    """病历CRUD操作"""
    
    async def get_by_patient(
        self, 
        db: AsyncSession, 
        *, 
        patient_id: int, 
        skip: int = 0, 
        limit: int = 100
    ) -> List[MedicalRecord]:
        """
        获取指定患者的病历列表
        
        Args:
            db: 数据库会话
            patient_id: 患者ID
            skip: 跳过的数量
            limit: 返回的最大数量
        
        Returns:
            病历列表
        """
        result = await db.execute(
            select(self.model)
            .where(self.model.patient_id == patient_id)
            .where(self.model.status == 1)
            .order_by(desc(self.model.created_at))
            .offset(skip)
            .limit(limit)
        )
        return list(result.scalars().all())
    
    async def get_latest_by_patient(
        self, 
        db: AsyncSession, 
        *, 
        patient_id: int
    ) -> Optional[MedicalRecord]:
        """
        获取患者最新的病历
        
        Args:
            db: 数据库会话
            patient_id: 患者ID
        
        Returns:
            最新的病历对象或None
        """
        result = await db.execute(
            select(self.model)
            .where(self.model.patient_id == patient_id)
            .where(self.model.status == 1)
            .order_by(desc(self.model.created_at))
            .limit(1)
        )
        return result.scalar_one_or_none()
    
    async def get_recent(
        self, 
        db: AsyncSession, 
        *, 
        days: int = 7,
        skip: int = 0, 
        limit: int = 100
    ) -> List[MedicalRecord]:
        """
        获取最近N天的病历
        
        Args:
            db: 数据库会话
            days: 天数
            skip: 跳过的数量
            limit: 返回的最大数量
        
        Returns:
            病历列表
        """
        from datetime import datetime, timedelta, timezone
        
        date_from = datetime.now(timezone.utc) - timedelta(days=days)
        result = await db.execute(
            select(self.model)
            .where(self.model.created_at >= date_from)
            .where(self.model.status == 1)
            .order_by(desc(self.model.created_at))
            .offset(skip)
            .limit(limit)
        )
        return list(result.scalars().all())
    
    async def query_by_conditions(
        self,
        db: AsyncSession,
        *,
        patient_id: Optional[int] = None,
        patient_name: Optional[str] = None,
        stroke_location: Optional[str] = None,
        paralysis_location: Optional[str] = None,
        skip: int = 0,
        limit: Optional[int] = None,
        order_by_desc: bool = True
    ) -> Tuple[List[MedicalRecord], int]:
        """
        条件查询病历（支持分页）
        
        Args:
            db: 数据库会话
            patient_id: 患者ID（可选）
            patient_name: 患者姓名（模糊查询，可选）
            stroke_location: 卒中部位（可选）
            paralysis_location: 瘫痪部位（可选）
            skip: 跳过的数量
            limit: 返回的最大数量（None 表示不限制）
            order_by_desc: 是否按更新时间降序排序
        
        Returns:
            (病历列表, 总数)
        """
        # 构建查询条件
        query = select(self.model).where(self.model.status == 1)
        
        # 患者姓名模糊查询
        if patient_name:
            query = query.where(self.model.patient_name.ilike(f"%{patient_name}%"))
        
        # 患者ID查询
        if patient_id:
            query = query.where(self.model.patient_id == patient_id)
        
        # 卒中部位查询
        if stroke_location:
            query = query.where(self.model.stroke_location == stroke_location)
        
        # 瘫痪部位查询
        if paralysis_location:
            query = query.where(self.model.paralysis_location == paralysis_location)
        
        # 计算总数
        count_query = select(func.count()).select_from(query.subquery())
        total_result = await db.execute(count_query)
        total = total_result.scalar() or 0
        
        # 排序
        if order_by_desc:
            query = query.order_by(desc(self.model.updated_at))
        else:
            query = query.order_by(asc(self.model.updated_at))
        
        # 分页
        query = query.offset(skip)
        if limit:
            query = query.limit(limit)
        
        # 执行查询
        result = await db.execute(query)
        records = list(result.scalars().all())
        
        return records, total
    
    async def get_by_ids(
        self,
        db: AsyncSession,
        *,
        record_ids: List[int]
    ) -> List[MedicalRecord]:
        """
        根据ID列表批量查询病历
        
        Args:
            db: 数据库会话
            record_ids: 病历ID列表
        
        Returns:
            病历列表
        """
        result = await db.execute(
            select(self.model)
            .where(self.model.id.in_(record_ids))
            .where(self.model.status == 1)
            .order_by(asc(self.model.created_at))
        )
        return list(result.scalars().all())
    
    async def get_by_patient_ids(
        self,
        db: AsyncSession,
        *,
        patient_ids: List[int]
    ) -> List[MedicalRecord]:
        """
        根据患者ID列表查询所有病历
        
        Args:
            db: 数据库会话
            patient_ids: 患者ID列表
        
        Returns:
            病历列表
        """
        result = await db.execute(
            select(self.model)
            .where(self.model.patient_id.in_(patient_ids))
            .where(self.model.status == 1)
            .order_by(asc(self.model.created_at))
        )
        return list(result.scalars().all())


# 创建全局实例
medical_record = CRUDMedicalRecord(MedicalRecord)

